package nckd.wnqjt.tmc.tda.plugin.form;

import kd.bos.algo.DataSet;
import kd.bos.algo.Row;
import kd.bos.dataentity.entity.DynamicObject;
import kd.bos.entity.report.AbstractReportListDataPlugin;
import kd.bos.entity.report.ReportQueryParam;
import kd.bos.orm.query.QCP;
import kd.bos.orm.query.QFilter;
import kd.bos.servicehelper.QueryServiceHelper;

import java.util.ArrayList;
import java.util.List;

/**
 * @author zhujintao
 * @date 2024/11/05
 * @desc 资金总额明细报表
 */
public class TotalFundsDetailListDataPlugin extends AbstractReportListDataPlugin {

    @Override
    public DataSet query(ReportQueryParam reportQueryParam, Object o) throws Throwable {

        //先获取符合条件的账户列表
        QFilter acctstatusfilter = new QFilter("acctstatus", QCP.not_equals, "closed");
        String getAcctSelectedFieds = "id,company,openorg,issetbankinterface,bank.bank_cate bankbankcate,bankaccountnumber,isvirtual,name,currencyname,defaultcurrency,opendate,acctstatus,acctstyle,accttype,acctproperty,manager,acctname,finorgtype";
        DataSet acctDataSet = QueryServiceHelper.queryDataSet(this.getClass().getName(), "bd_accountbanks", getAcctSelectedFieds, acctstatusfilter.toArray(), null);

        /**
         * ***************************************************************
         */
        QFilter finorgtype = new QFilter("number", QCP.equals, "FI-015");
        DynamicObject queryOne = QueryServiceHelper.queryOne("bd_finorgtype", "id", finorgtype.toArray());
        QFilter beiBankbalanceFilter = null;
        if (queryOne != null) {
            Object pkValue = queryOne.getLong("id");
            beiBankbalanceFilter = new QFilter("bank.finorgtype.id", QCP.not_equals, pkValue);
        }
        //QFilter beiBankbalanceFilter = new QFilter("bank.name", QCP.not_like, "结算中心");
        String beiBankbalanceSelectField = "id,company.id companyid,company.number companynumber,company.name companyname" +
                ",accountbank.id accountbankid,accountbank.bankaccountnumber bankaccountnumber,bizdate,nckd_depositbalance,amount,freezebalance,datasource";
        DataSet beiBankbalanceDataSet = QueryServiceHelper.queryDataSet(this.getClass().getName(), "bei_bankbalance", beiBankbalanceSelectField, beiBankbalanceFilter.toArray(), null);
        //取 账号维度的 最大日期的 当前余额
        DataSet beiBankbalanceGroupDataSet = beiBankbalanceDataSet.groupBy(new String[]{"companyid", "bankaccountnumber"})
                .maxP("bizdate", "freezebalance")
                .maxP("bizdate", "amount")
                .maxP("bizdate", "datasource")
                .finish();

        //获取虚拟户
        DataSet filterAcctDataSet = acctDataSet.copy().filter("isvirtual = true");
        List<String> isvirtualAccountNoList = new ArrayList<>();
        while (filterAcctDataSet.hasNext()) {
            Row next = filterAcctDataSet.next();
            String bankaccountnumber = next.getString("bankaccountnumber");
            isvirtualAccountNoList.add(bankaccountnumber);
        }
        //受限资金管理
        String amRestrictedfundsmanagerSelectField = "id,org.id companyid,org.number companynumber,org.name companyname,bankacct.id accountbankid,bankacct.bankaccountnumber bankaccountnumber,unrestrictedamt";
        //增加受限
        QFilter businesstype = new QFilter("businesstype", QCP.equals, "1").and("billstatus", QCP.equals, "C")
                .and("bankacct.bankaccountnumber", QCP.in, isvirtualAccountNoList);
        DataSet amRestrictedfundsmanagerDataSet = QueryServiceHelper.queryDataSet(this.getClass().getName(), "am_restrictedfundsmanager", amRestrictedfundsmanagerSelectField, businesstype.toArray(), null);

        //定期存款处理
        QFilter cimDepositQfilter = new QFilter("billstatus", QCP.equals, "C").and("finaccount", QCP.in, isvirtualAccountNoList);
        String cimDepositSelectField = "id,org.id companyid,org.number companynumber,org.name companyname,settleaccount.id accountbankid,settleaccount.bankaccountnumber bankaccountnumber,surplusamount";
        DataSet cimDepositDataSet = QueryServiceHelper.queryDataSet(this.getClass().getName(), "cim_deposit", cimDepositSelectField, cimDepositQfilter.toArray(), null);
        DataSet finish = acctDataSet.leftJoin(beiBankbalanceGroupDataSet).on("company", "companyid").on("bankaccountnumber", "bankaccountnumber")
                .select(new String[]{"company", "bankaccountnumber", "issetbankinterface", "bankbankcate", "isvirtual", "finorgtype"}, new String[]{"amount", "freezebalance"}).finish()
                .leftJoin(amRestrictedfundsmanagerDataSet).on("company", "companyid").on("bankaccountnumber", "bankaccountnumber")
                .select(new String[]{"company", "bankaccountnumber", "issetbankinterface", "bankbankcate", "isvirtual", "finorgtype", "amount", "freezebalance"}, new String[]{"unrestrictedamt"}).finish()
                .leftJoin(cimDepositDataSet).on("company", "companyid").on("bankaccountnumber", "bankaccountnumber")
                .select(new String[]{"company", "bankaccountnumber", "issetbankinterface", "bankbankcate", "isvirtual", "finorgtype", "amount", "freezebalance", "unrestrictedamt"}, new String[]{"surplusamount"}).finish();
        finish = finish.updateField("amount", "case when amount is null then 0 else amount end");
        finish = finish.updateField("freezebalance", "case when freezebalance is null then 0 else freezebalance end");
        finish = finish.updateField("unrestrictedamt", "case when unrestrictedamt is null then 0 else unrestrictedamt end");
        finish = finish.updateField("surplusamount", "case when surplusamount is null then 0 else surplusamount end");
        finish = finish.addField("amount + unrestrictedamt + surplusamount", "totalfunds");
        finish = finish.addField("freezebalance + unrestrictedamt + surplusamount", "restrictedfunds");
        finish = finish.addField("totalfunds - restrictedfunds", "availablefunds");
        finish = finish.addField("0", "sort");
        DataSet deatil = finish.select("company", "bankaccountnumber", "bankbankcate", "totalfunds", "restrictedfunds", "availablefunds", "sort");
        DataSet total = deatil.copy().groupBy(new String[]{"bankbankcate"}).sum("totalfunds").sum("restrictedfunds").sum("availablefunds").finish();
        total = total.addNullField("company");
        total = total.addField("'小计'", "bankaccountnumber");
        total = total.addField("1", "sort");
        DataSet result = deatil.select("bankbankcate", "company", "bankaccountnumber", "totalfunds", "restrictedfunds", "availablefunds", "sort")
                .union(total.select("bankbankcate", "company", "bankaccountnumber", "totalfunds", "restrictedfunds", "availablefunds", "sort"));
        return result.orderBy(new String[]{"bankbankcate", "sort"});
    }
}